import scrapy
from faker import Faker
from mztr.items import StrItem
from mztr.tor01 import *
import random
import time
import json
import math
import pymysql
import socket
import socks
import os

class PylpSpider(scrapy.Spider):
	name = 'pylp'
	allowed_domains = ['meituan.com']
	start_urls = ['https://www.meituan.com/changecity/']


	# header={
	# # "User-Agent":'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/80.0.3987.163 Safari/537.36'
	# "User-Agent":Faker(locale='zh_CN').user_agent()
	# }

	comments_list = []
	tags = []
	db_conn = None
	cursor = None

	isvi = 100

	# 连接数据库
	def connect_db(self):
		try :
			db = 'test'
			host = 'localhost'
			port = 3306
			user =  'root'
			passwd = '1234'

			# 建立数据库连接
			self.db_conn = pymysql.connect(host=host, port=port, db=db, user=user, passwd=passwd, charset='utf8mb4')
			# 获取游标
			self.cursor = self.db_conn.cursor()
			print("mysql connet successful")
		except:
			print("pymysql connect have something wrong")


	def start_requests(self):

		self.connect_db()

		sql = "select shopId,allCommentNum from pds"
		self.cursor.execute(sql)
		results = self.cursor.fetchall()

		for shopId,allCommentNum in results:
			all_page = int(math.ceil(float(allCommentNum)/10))
			for page in range(all_page+1):
				# if self.isvi == 100 :
				# 	self.isvi = 0
				# 	get_IP()
					# res = os.system("sudo /etc/init.d/tor restart")
					# if res == 0:
					# 	socks.set_default_proxy(socks.SOCKS5,"127.0.0.1",9050)
					# 	socket.socket = socks.socksocket
					# 	print("更换ip成功")
					# # print("IP:"+proxy)
				while True:
					url = "https://www.meituan.com/meishi/api/poi/getMerchantComment?uuid=7baf5496-2612-48b2-9de2-17957e209630&platform=1&partner=126&originUrl=https://www.meituan.com/meishi/"+str(shopId)+"/&riskLevel=1&optimusCode=10&id="+str(shopId)+"&userId=&offset="+str(page*10)+"&pageSize=10&sortType=1"
					# yield scrapy.Request(url,headers=self.header,callback=self.parse,dont_filter=True,meta={'shopId':shopId,'page':page,'all_page':all_page})
					# yield scrapy.Request(url,callback=self.parse,dont_filter=True,meta={'proxy':"http://localhost:9050",'shopId':shopId,'page':page,'all_page':all_page})
					yield scrapy.Request(url,callback=self.parse,dont_filter=True,meta={'shopId':shopId,'page':page,'all_page':all_page})

		self.close_db()



	def parse(self,response):
		self.isvi += 1
		shopId = response.meta['shopId']
		page = response.meta['page']
		all_page = response.meta['all_page']
		data = json.loads(response.text)['data']
		print("scrapying page in:"+str(page)+"/"+str(all_page)+" at "+str(shopId))
		if page == 0:
			all_tags = data['tags']
			if all_tags is not None:
				for t in all_tags:
					self.tags.append({'tag':t['tag'],'count':t['count']})
				strItem = StrItem()
				strItem['shopId'] = shopId
				strItem['commentTags'] = json.dumps(self.tags,ensure_ascii=False)
				# strItem['comments'] = json.dumps(self.comments_list,ensure_ascii=False)
				self.insert_data_tags(strItem)

		comments = data['comments']

		if comments is not None:
			for comment in comments:
				if comment['comment'] is None:
					continue
				comm = comment['comment']
				commentTime = comment['commentTime']
				star = comment['star']
				reviewId = comment['reviewId']
				self.comments_list.append(reviewId)
				dic = {'reviewId':reviewId,'comment':comm,'commentTime':commentTime,'star':star}
				self.insert_data_review(dic)

		if page == all_page-1 :
			# strItem = StrItem()
			# strItem['shopId'] = shopId
			# strItem['commentTags'] = json.dumps(self.tags,ensure_ascii=False)
			strItem['comments'] = json.dumps(self.comments_list,ensure_ascii=False)
			self.insert_data_comments(shopId,strItem)
			print("scrapy page end of "+str(shopId))

	def insert_data_comments(self,shopId,comments_list):
		try:
			# self.cursor.execute("update pds set commentTags=%s where shopId="+str(item['shopId']),item['commentTags'])
			self.cursor.execute("update pds set comments=%s where shopId="+str(shopId),comments_list)
			self.db_conn.commit()
			print("shop_comments_data insert successful")
		except:
			print("comments_data insert failure")
			self.db_conn.rollback()		# 回滚数据



	def insert_data_review(self,dic):

		reviewId = dic['reviewId']
		comment = dic['comment']
		commentTime = dic['commentTime']
		star = dic['star']

		data = (int(reviewId),str(comment),int(commentTime),int(star))

		sql = "insert into pds_review values(%s,%s,%s,%s)"

		print(data)
		try:
			self.cursor.execute(sql,data)
			self.db_conn.commit()
			print("review_data insert successful")
		except:
			print("review_data insert failure")
			self.db_conn.rollback()		# 回滚数据


	def insert_data_tags(self,item):
		try:
			self.cursor.execute("update pds set commentTags=%s where shopId="+str(item['shopId']),item['commentTags'])
			# self.cursor.execute("update pds set comments=%s where shopId="+str(item['shopId']),item['comments'])
			self.db_conn.commit()
			print("shop_tags_data insert successful")
		except:
			print("shop_tags_data insert failure")
			self.db_conn.rollback()		# 回滚数据


	# 关闭数据库连接
	def close_db(self):
		self.cursor.close()
		self.db_conn.close()
		print("pymysql connect close successful")
